﻿using System;
using System.Drawing;
using System.Text.RegularExpressions;
using GrapeCity.Documents.Excel;
using GrapeCity.Documents.Excel.Drawing;


namespace GrapeCity.Documents.BalanceSheetSample
{
    class Program
    {
        static void Main(string[] args)
        {
            //Step 1 - Workbook Initialization
            Workbook workbook = new Workbook();
            workbook.Open("CustomerOrderHistory.xlsx");

            //Step 2 - Get the Worksheet
            IWorksheet worksheet;
            worksheet = workbook.Worksheets["data"]; //OR workbook.Worksheets[0];

            //Step 3 - Get the unique list of customer names (for master dropdown)
            IRange rngUniqueCustomerNames;
            rngUniqueCustomerNames = worksheet.Range["T3"]; //dummy range to get unique list of customer names
            rngUniqueCustomerNames.Formula2 = "=UNIQUE($B$2:$B$2156)";

            //Step 4 - Create the master dropdown
            IValidation customerNameList = worksheet.Range["L3"].Validation;
            customerNameList.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal,"=$T$3#");

            //Step 5 - Fetch the list of unique OrderIDs(for dependent dropdown)
            workbook.Names.Add("CustomerName", "=$L$3");
            workbook.Names.Add("Unique_Cus_Order_combo", "=UNIQUE(data!$A$2:$B$2156)");

            IRange rngUniqueOrderIds;
            rngUniqueOrderIds = worksheet.Range["V2"]; //dummy rnage to get unique list of customer names
            rngUniqueOrderIds.Formula2 = "=CHOOSECOLS(FILTER(Unique_Cus_Order_combo, CHOOSECOLS(Unique_Cus_Order_combo,2)=CustomerName), 1)";

            //Step 6 - Populate the dependent dropdown
            IValidation orderIdList = worksheet.Range["L6"].Validation;
            orderIdList.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal, "=$v$2#");

            //Step 7 - Set the default values to dropdown and save the workbook
            worksheet.Range["L3"].Value = "Paul Henriot";
            worksheet.Range["L6"].Value = 10248;

            workbook.Save("CustomerOrderHistoryReport.xlsx");


        }

    }
}